package org.example.now.async.downloadexcel;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import org.example.old.dto.SysDto;
import org.example.old.service.SysService;
import org.example.old.vo.SysVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/sys4")
public class FillExcelData {

    @Autowired
    SysService sysService;

    /**
     * 全量查询数据
     * @return
     */
    public List<SysVo> data() {
        return sysService.querInfoList();
    }


    /**
     * 最简单的填充--easyexcel
     */
    @GetMapping("/queryList")
    public void queryList() {
        String sheetName = "Sheet1";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String filePath = "D:/demo.xlsx";
        // 方案1：根据对象填充
        String filePath2 = "D:/demo2.xlsx";
//        SysDto fillData = new SysDto();
//        fillData.setUserCode("333333");
//        fillData.setUserName("张三");
//        EasyExcel.write(filePath2).withTemplate(filePath).sheet().doFill(fillData);

        // 方案2：map填充
        Map<String, Object> map = MapUtils.newHashMap();
        map.put("userName", "张三");
        map.put("userCode", "44444");
        EasyExcel.write(filePath2).withTemplate(filePath).sheet().doFill(map);
    }


    /**
     * 填充列表--easyexcel
     */
    @GetMapping("/queryList2")
    public void queryList2() {
        String sheetName = "Sheet1";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String filePath = "D:/demo.xlsx";
        // 方案1：根据对象填充
        String filePath2 = "D:/demo2.xlsx";
        EasyExcel.write(filePath2).withTemplate(filePath).sheet().doFill(data());

    }

    /**
     * 复杂的填充--easyexcel
     */
    @GetMapping("/queryList3")
    public void queryList3() {
        String sheetName = "Sheet1";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String filePath = "D:/demo.xlsx";
        // 方案1：根据对象填充
        String filePath2 = "D:/demo2.xlsx";

        try(ExcelWriter excelWriter = EasyExcel.write(filePath2).withTemplate(filePath).build()){
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 入参用了forceNewRow代表在写入list的时候不管list下面有没有空行，都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(data(),fillConfig,writeSheet);
            excelWriter.fill(data(),fillConfig,writeSheet);
            Map<String,Object> map = MapUtils.newHashMap();
            map.put("total",1000);
            excelWriter.fill(map,writeSheet);
        }

    }

    /**
     * 数据量大的复杂填充--easyexcel
     */
    @GetMapping("/queryList4")
    public void queryList4() {
        String sheetName = "Sheet1";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String filePath = "D:/demo.xlsx";
        // 方案1：根据对象填充
        String filePath2 = "D:/demo2.xlsx";

        try(ExcelWriter excelWriter = EasyExcel.write(filePath2).withTemplate(filePath).build()){
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(data(),writeSheet);
            excelWriter.fill(data(),writeSheet);
            List<SysVo> list = new ArrayList<>();
            SysVo sysVo = new SysVo();
            sysVo.setTotal(1000);
            list.add(sysVo);
            excelWriter.write(list,writeSheet);
        }
    }


    /**
     * 横向的填充--easyexcel
     */
    @GetMapping("/queryList5")
    public void queryList5() {
        String sheetName = "Sheet1";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String filePath = "D:/demo1.xlsx";
        // 方案1：根据对象填充
        String filePath2 = "D:/demo3.xlsx";

        try (ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(filePath2).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(data(), fillConfig, writeSheet);
            excelWriter.fill(data(), fillConfig, writeSheet);
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("total", 1000);
            excelWriter.fill(map, writeSheet);
        }
    }

        /**
         * 多列表组合填充填充--easyexcel
         */
        @GetMapping("/queryList6")
        public void queryList6() {
            String sheetName = "Sheet1";
            // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
            String filePath = "D:/demo1.xlsx";
            // 方案1：根据对象填充
            String filePath2 = "D:/demo3.xlsx";

            try (ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(filePath2).build()) {
                WriteSheet writeSheet = EasyExcel.writerSheet().build();
                FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
                excelWriter.fill(new FillWrapper("data1",data()), fillConfig, writeSheet);
                excelWriter.fill(new FillWrapper("data2",data()),  writeSheet);
                Map<String, Object> map = MapUtils.newHashMap();
                map.put("total", 1000);
                excelWriter.fill(map, writeSheet);
            }
        }

}
